package com.aotain.oframework.dao.sqlt;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import org.springframework.util.Assert;

import com.aotain.oframework.dto.PageDTO;
import com.aotain.oframework.exception.SqlTemplateParseException;

public class JpaSqltRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements JpaSqltRepository<T, ID> {

	private EntityManager entityManager;
	private final static Logger logger = LoggerFactory.getLogger(JpaSqltRepositoryImpl.class);
	private static final String PARAM_SEPARATOR = "_";

	public JpaSqltRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
		super(entityInformation, entityManager);
		this.entityManager = entityManager;
	}

	public JpaSqltRepositoryImpl(Class<T> domainClass, EntityManager em) {
		super(domainClass, em);
		this.entityManager = em;
	}

	@Override
	public String getJpqlTemplate(String namespace, String sqltId, Object criteria) {
		return SqlTemplateEngine.getInstance().getJpql(namespace, sqltId, criteria);
	}

	@Override
	public String getNativeSqlTemplate(String namespace, String sqltId, Object criteria) {
		return SqlTemplateEngine.getInstance().getNativeSql(namespace, sqltId, criteria);
	}

	@Override
	public Object queryNativeSqlForObject(String namespace, String sqltId, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createNativeQuery(qlString);
		setParameters(query, qlString, criteria);
		return query.getSingleResult();
	}

	@Override
	public List queryNativeSqlForList(String namespace, String sqltId, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createNativeQuery(qlString);
		setParameters(query, qlString, criteria);
		return query.getResultList();
	}

	public List queryNativeSqlForList(String namespace, String sqltId, Object[] criteria) {
		String qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createNativeQuery(qlString);
		setParameters(query, qlString, criteria);
		return query.getResultList();
	}

	@Override
	public List queryNativeSqlForPage(String namespace, String sqltId, Object criteria, Pageable pageable) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createNativeQuery(qlString);
		setParameters(query, qlString, criteria);
		query.setFirstResult(pageable.getOffset());
		query.setMaxResults(pageable.getPageSize());
		return query.getResultList();
	}

	@Override
	public Object queryJpqlForObject(String namespace, String sqltId, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getJpqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createQuery(qlString);
		setParameters(query, qlString, criteria);
		return query.getSingleResult();
	}

	@Override
	public Integer queryJpqlRowCount(String namespace, String sqltId, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getJpqlTemplate(namespace, sqltId, criteria);
		String countQlString = "select count(*) from (" + qlString + ") temp";
		Query query = this.entityManager.createQuery(countQlString);
		setParameters(query, countQlString, criteria);
		return (Integer) query.getSingleResult();
	}

	@Override
	public Integer queryNativeSqlRowCount(String namespace, String sqltId, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria);
		String countQlString = "SELECT count(*) FROM ( " + qlString + " ) TEMP";
		Query query = this.entityManager.createNativeQuery(countQlString);
		setParameters(query, countQlString, criteria);
		return (Integer) query.getSingleResult();
	}

	@Override
	public List queryJpqlForList(String namespace, String sqltId, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getJpqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createQuery(qlString);
		setParameters(query, qlString, criteria);
		return query.getResultList();
	}

	@Override
	public List queryJpqlForPage(String namespace, String sqltId, Object criteria, Pageable pageable) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getJpqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createQuery(qlString);
		setParameters(query, qlString, criteria);
		query.setFirstResult(pageable.getOffset());
		query.setMaxResults(pageable.getPageSize());
		return query.getResultList();
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List queryJpqlForPage2(String namespace, String sqltId, PageDTO pageDTO) {
		String qlString = this.getJpqlTemplate(namespace, sqltId, pageDTO);
		Query query = this.entityManager.createQuery(qlString);
		int offsetIndex = pageDTO.getOffset();
		setParameters(query, qlString, pageDTO.getCriteria());
		query.setFirstResult(offsetIndex);
		query.setMaxResults(pageDTO.getPagesize());
		return query.getResultList();
	}

	public List queryJpqlForReceptionRecord(String namespace, String sqltId, PageDTO pageDTO) {
		String qlString = this.getJpqlTemplate(namespace, sqltId, pageDTO);
		Query query = this.entityManager.createQuery(qlString);
		setParameters(query, qlString, pageDTO.getCriteria());
		return query.getResultList();
	}

	public void setParameters(Query query, String qlString, Object criteria) {
		List<String> params = this.resolveNamedParameters(qlString);
		try {
			for (String param : params) {
				Object value = resolveParameterValue(param, criteria);
				query.setParameter(param, value);
				logger.trace("setting query parameter: {} = {}", param, value);
			}
		} catch (SqlTemplateParseException e) {
			throw new RuntimeException(e);
		}
	}

	public static Map<String, Object> buildCriteriaMap(String qlString, Object criteria) {
		Map<String, Object> map = new HashMap<String, Object>();
		List<String> params = resolveNamedParameters(qlString);
		try {
			for (String param : params) {
				Object value = resolveParameterValue(param, criteria);
				map.put(param, value);
				logger.trace("setting query parameter: {} = {}", param, value);
			}
		} catch (SqlTemplateParseException e) {
			throw new RuntimeException(e);
		}
		return map;
	}

	private static void setParameters(Query query, List<String> params, Object[] criteria) {
		try {
			int i = 0;
			for (Object o : criteria) {
				for (String param : params) {
					Object value = resolveParameterValue(param, o);
					query.setParameter(param + i, value);
					logger.trace("setting query parameter: {} = {}", param + i, value);
				}
				i++;
			}
		} catch (SqlTemplateParseException e) {
			throw new RuntimeException(e);
		}
	}

	private static void setParameters(Query query, String qlString, Object[] criteria) {
		List<String> params = resolveNamedParameters(qlString);
		int i = 0;
		for (String param : params) {
			query.setParameter(param, criteria[i]);
			logger.trace("setting query parameter: {} = {}", param, criteria[i]);
			i++;
		}
	}

	private static List<String> resolveNamedParameters(String qlString) {
		List<String> params = new ArrayList<String>(0);
		String[] qlStrs = StringUtils.splitByWholeSeparator(qlString, null);
		for (String ql : qlStrs) {
			if (ql != null && ql.startsWith(":")) {
				params.add(ql.replaceFirst(":", ""));
			}
		}
		return params;
	}

	private static Object resolveParameterValue(String property, Object object) {
		try {
			if (property.indexOf(PARAM_SEPARATOR) != -1) {
				String resolvedProp = property.replaceAll(PARAM_SEPARATOR, ".");
				return PropertyUtils.getNestedProperty(object, resolvedProp);
			} else {
				return PropertyUtils.getProperty(object, property);
			}
		} catch (IllegalAccessException e) {
			throw new SqlTemplateParseException(e);
		} catch (InvocationTargetException e) {
			throw new SqlTemplateParseException(e);
		} catch (NoSuchMethodException e) {
			throw new SqlTemplateParseException(e);
		}
	}

	public Object handleFuzzyQueryCriteria(Object example) {
		List<Integer> checkList = new ArrayList<Integer>();
		return traverseCriteriaObject(example, checkList);
	}

	private Object traverseCriteriaObject(Object example, List<Integer> checkList) {
		if (example == null) {
			return null;
		}
		if (checkList.contains(example.hashCode())) {
			logger.trace("Object {} has been handled.", example);
			return example;
		} else {
			checkList.add(example.hashCode());
		}
		if (example instanceof Map) {
			logger.trace("BeanUtils can not clone Map object.");
			return example;
		}
		if (example instanceof Collection) {
			logger.trace("BeanUtils can not clone Collection object.");
			return example;
		}
		logger.trace("start to handle fuzzy query field....");
		Assert.notNull(example, "Example object can not be empty.");
		Object cle = example;
		try {
			logger.trace("Object before handling: {}", BeanUtils.describe(example).toString());
			cle = BeanUtils.cloneBean(example);
			Field[] fields = cle.getClass().getDeclaredFields();
			Method[] methods = cle.getClass().getDeclaredMethods();
			for (Field field : fields) {
				boolean isFuzzyField = field.isAnnotationPresent(FuzzyQuery.class);
				if (isFuzzyField) {
					trimFieldValue(example, field);
					trimAndFuzzyFieldValue(cle, field);
				} else {
					trimFieldValue(example, field);
					trimFieldValue(cle, field);
				}
				if (field.getType().isAnnotationPresent(Entity.class)) {
					logger.trace("Found entity attribute {}, start traversing PO field.", field.getName());
					Object value = null;
					field.setAccessible(true);
					value = field.get(example);
					if (value != null) {
						Object newVal = traverseCriteriaObject(value, checkList);
						field.set(cle, newVal);
					}
				}
			}
			for (Method method : methods) {
				method.setAccessible(true);
				boolean isFuzzyField = method.isAnnotationPresent(FuzzyQuery.class);
				if (isFuzzyField) {
					if (method.getReturnType().isAssignableFrom(String.class)) {
						Object value = null;
						method.setAccessible(true);
						value = method.invoke(example);
						String property = StringUtils.uncapitalize(method.getName().replaceFirst("get", StringUtils.EMPTY));
						if (!StringUtils.isBlank((String) value)) {
							Object newVal = "%" + StringUtils.trimToEmpty((String) value) + "%";
							BeanUtils.setProperty(cle, property, newVal);
						}
					}
				}

			}
			logger.trace("Object after handling: {}", BeanUtils.describe(cle) .toString());
		} catch (Exception e) {
			logger.error("Error handling fuzzy query.", e);
		}
		return cle;
	}

	private void trimFieldValue(Object example, Field field) throws Exception {
		if (field.getType().isAssignableFrom(String.class)) {
			Object value = null;
			field.setAccessible(true);
			value = field.get(example);
			if (!StringUtils.isBlank((String) value)) {
				Object newVal = StringUtils.trimToEmpty((String) value);
				field.set(example, newVal);
			}
		}
	}

	private void trimAndFuzzyFieldValue(Object example, Field field) throws Exception {
		if (field.getType().isAssignableFrom(String.class)) {
			Object value = null;
			field.setAccessible(true);
			value = field.get(example);
			if (!StringUtils.isBlank((String) value)) {
				Object newVal = "%" + StringUtils.trimToEmpty((String) value) + "%";
				field.set(example, newVal);
			}
		}
	}

	@Override
	public List queryNativeSqlForSortPage(String namespace, String sqltId, PageDTO pageDTO) {
		String qlString = this.getNativeSqlTemplate(namespace, sqltId, pageDTO);
		Query query = this.entityManager.createNativeQuery(qlString);
		setParameters(query, qlString, pageDTO.getCriteria());
		query.setFirstResult(pageDTO.getOffset());
		query.setMaxResults(pageDTO.getPagesize());
		return query.getResultList();
	}

	@Override
	public List queryNativeSqlForSortPage(String namespace, String sqltId, Object[] criteria, PageDTO pageDTO) {
		String sql = "";
		String qlString = "";
		String selectSql = "";
		for (int i = 0; i < criteria.length; i++) {
			if (i != 0) {
				sql += " union ";
			}
			qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria[i]);
			sql += qlString;
		}
		int idxFrom = qlString.indexOf("from");
		selectSql = qlString.substring(0, idxFrom);
		List<String> params = this.resolveNamedParameters(qlString);
		sql = "select tmp.* from ( " + sql + " )tmp order by tmp." + pageDTO.getColName() + " " + pageDTO.getSort();

		for (int i = 0; i < criteria.length; i++) {
			for (int j = 0; j < params.size(); j++) {
				sql = sql.replaceFirst(":" + params.get(j) + " ", ":" + params.get(j) + i + " ");
			}
		}
		Query query = this.entityManager.createNativeQuery(sql);
		setParameters(query, params, criteria);
		query.setFirstResult(pageDTO.getOffset());
		query.setMaxResults(pageDTO.getPagesize());
		return query.getResultList();
	}

	@Override
	public Integer queryNativeSqlForObjsCount(String namespace, String sqltId, Object[] criteria) {
		String sql = "";
		String qlString = "";
		String selectSql = "";
		for (int i = 0; i < criteria.length; i++) {
			if (i != 0) {
				sql += " union ";
			}
			qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria[i]);
			sql += qlString;
		}
		int idxFrom = qlString.indexOf("from");
		selectSql = qlString.substring(0, idxFrom);
		List<String> params = this.resolveNamedParameters(qlString);
		sql = "select count(1) from ( " + sql + " )tmp ";
		for (int i = 0; i < criteria.length; i++) {
			for (int j = 0; j < params.size(); j++) {
				sql = sql.replaceFirst(":" + params.get(j) + " ", ":" + params.get(j) + i + " ");
			}
		}
		Query query = this.entityManager.createNativeQuery(sql);
		setParameters(query, params, criteria);
		return (Integer) query.getSingleResult();
	}

	@Override
	public List queryNativeSqlForList(String sql, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		Query query = this.entityManager.createNativeQuery(sql);
		setParameters(query, sql, criteria);
		return query.getResultList();
	}

	@Override
	public int queryNativeSql(String sql, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		Query query = this.entityManager.createNativeQuery(sql);
		setParameters(query, sql, criteria);
		return query.executeUpdate();
	}

	@Override
	public Object queryNativeSqlForObject(String sql, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		Query query = this.entityManager.createNativeQuery(sql);
		setParameters(query, sql, criteria);
		return query.getSingleResult();
	}

	@Override
	public List queryNativeSqlForPage(String sql, Object criteria, PageDTO pageDTO) {
		criteria = handleFuzzyQueryCriteria(criteria);
		Query query = this.entityManager.createNativeQuery(sql);
		setParameters(query, sql, criteria);
		query.setFirstResult(pageDTO.getOffset());
		query.setMaxResults(pageDTO.getPagesize());
		return query.getResultList();
	}

	@Override
	public int executeNativeSql(String namespace, String sqltId, Object criteria) {
		criteria = handleFuzzyQueryCriteria(criteria);
		String qlString = this.getNativeSqlTemplate(namespace, sqltId, criteria);
		Query query = this.entityManager.createNativeQuery(qlString);
		setParameters(query, qlString, criteria);
		return query.executeUpdate();
	}

}
